package org.anyline.simple.dml;

import org.anyline.data.param.Config;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.TableBuilder;
import org.anyline.data.param.init.DefaultConfig;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.entity.*;
import org.anyline.entity.generator.GeneratorConfig;
import org.anyline.entity.generator.PrimaryGenerator;
import org.anyline.metadata.Table;
import org.anyline.proxy.ServiceProxy;
import org.anyline.proxy.TransactionProxy;
import org.anyline.service.AnylineService;
import org.anyline.simple.dml.entity.User;
import org.anyline.util.BeanUtil;
import org.anyline.util.ConfigTable;
import org.anyline.util.regular.Regular;
import org.anyline.util.regular.RegularUtil;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.*;

@SpringBootTest
public class DMLTest {
    private Logger log = LoggerFactory.getLogger(DMLTest.class);

    @Autowired
    private AnylineService service          ;

    @Test
    public void cache(){
        DataSet querys = ServiceProxy.querys(TableBuilder.init("SSO_USER").build(), new DefaultConfigStore()
        .columns( "NAME as 'username'", "count(1) as 'num'")
        .eq("DEPT_ID", "1")
        .group("NAME")
    );
    }
    @Test
    public void decimal(){
        DataRow row = new DataRow();
        row.put("d", 11.1);
        service.insert("a2", row);
        DataSet set = service.querys("a2");
        DataSet s = set.select.startWith("NM","1");
        s.desc("D");
        System.out.println(s.concat("D"));

    }

    @Test
    public void test_decimal() throws Exception {
        ConfigTable.IS_LOG_QUERY_RESULT = true;
        TransactionProxy.start();
        DataRow row = new DataRow();
        row.put("SALARY", 100);
        row.put("SALARY_12", 100);
        long qty = service.insert("CRM_USER", row);
        System.out.println(qty);
        System.out.println(service.querys("CRM_USER(AVG(PRICE))").getMetadatas());
        System.out.println(service.querys("CRM_USER(PRICE)").getMetadatas());
        ;
    }
    @Test
    public void empty(){
        ConfigTable.IS_LOG_QUERY_RESULT = true;
        ConfigStore configs = new DefaultConfigStore();
        //configs.isNull("ID");
       configs.notNull("CODE");
       // configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "ID1" , "");
        //configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, Compare.NOT_EQUAL, "ID2" , "");
       // configs.and(Compare.EMPTY_VALUE_SWITCH.NULL,Compare.NOT_NULL, "M.CODE");
        service.querys("CRM_USER", new DefaultConfigStore().and(configs));
    }
    @Test
    public void tables(){
        ConfigTable.IS_LOG_QUERY_RESULT = true;
        DataSet set = service.querys(TableBuilder.init("CRM_USER(M.ID,COUNT(*) AS QTY) AS M")
            .left("CRM_USER D","M.ID = D.ID").build()//.group("M.ID").having("COUNT(*)>0")
            , new DefaultConfigStore().group("M.ID").having("COUNT(*)>0")
            , "M.ID:>=100")
        ;
        System.out.println(set);
    }
    @Test
    public void pk(){
        List<Map> maps = new ArrayList<>();
        for(int i=0; i<100; i++) {
            Map map = new HashMap();
            map.put("user_id", i);
            map.put("user_code", i*100);
            maps.add(map);
        }

        service.update( 10, "crm_user", maps, new DefaultConfigStore().keys("user_id"));
    }
    @Test
    public void catchException(){
        try {
            ConfigTable.IS_LOG_QUERY_RESULT = true;
            DataSet set = service.querys("SELECT * FROM 1");
            System.out.println("查询结果:"+set.isSuccess());
        }catch (Exception e){
            System.out.println("查询异常:"+e);
        }
    }

    @Test
    public void group(){
        TableBuilder builder = new TableBuilder();
        builder.from("CRM_USER AS A1");
        ConfigStore configs = new DefaultConfigStore();
        configs.columns("ID, COUNT(ID) AS QTY");
        configs.group("A1.ID");
        service.query(builder.build(), configs);
    }

    @Test
    public void page(){
        ConfigTable.IS_LOG_QUERY_RESULT = true;
        DataSet set = new DataSet();
        for(int i=0; i<20; i++){
            DataRow row = set.add();
            row.put("CODE", i);
        }
        service.insert("crm_user", set);
        service.querys("crm_user", new DefaultConfigStore().limit(10,2));
    }

    @Test
    public void order(){
        ConfigTable.IS_LOG_QUERY_RESULT = true;
        DataSet set = service.querys("Platform");
        set.asc("id");
        PageNavi navi = new DefaultPageNavi();
        navi.order("ID","DESC");
        service.querys("CRM_USER", navi);

        String sql = "SELECT * FROM CRM_USER AS M LEFT JOIN CRM_USER AS F ON M.ID = F.ID";
        ConfigStore configs = new DefaultConfigStore();
        configs.order("M.ID", "DESC");
        service.querys(sql, configs);
    }
    @Test
    public void metadata(){
        DataSet set = service.querys("SELECT * FROM CRM_USER WHERE ID IS NULL");

        System.out.println(set.getMetadatas());
        System.out.println(service.metadata("SELECT * FROM CRM_USER WHERE ID IS NULL"));
    }
    @Test
    public void updatePrimary(){
        DataRow row = new DataRow();
        row.put("ID", 10);
        row.put("STATUS",1);
        row.put("CODE", 2);
        ConfigStore configs = new DefaultConfigStore();
        configs.and("STATUS", 20);
        configs.and("ID", 30);

        //确定更新条件的 按指定条件更新,
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2 WHERE 1=1 AND ID = 20 AND STATUS = 30
       // service.update("CRM_USER", row, configs);

        //默认情况下主键值不更新,通过+ID强制更新
        //最后参数表示更新哪几列,注意ID与+ID的区别, ID表示只更新这一列， +ID表示其他规则不变，只是多更新一列ID
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2, ID = 10 WHERE 1=1 AND ID = 20 AND STATUS = 30
        service.update("CRM_USER", row, configs, "+ID");

    }
    @Test
    public void updateRow(){
        DataRow row = new DataRow();
        row.put("NAME", "Z");
        service.update("CRM_USER", row);
        service.delete("CRM_USER", row);
        row = new DataRow();
        row.put("ID", 10);
        row.put("STATUS",1);
        row.put("CODE", 2);
        ConfigStore configs = new DefaultConfigStore();
        configs.and("STATUS", 3);

        //确定更新条件的 按指定条件更新
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2 WHERE 1=1 AND STATUS = 3
        service.update("CRM_USER", row, configs);

        //没有指定条件的 按主键更新
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2 WHERE 1=1 AND ID = 10
        service.update("CRM_USER", row);
        row = service.query("CRM_USER");
        row.put("CODE", 3);
        service.update(row);

    }
    @Test
    public void updateMap(){
        Map row = new HashMap();
       // row.put("ID", 10);
        row.put("STATUS",1);
        row.put("CODE", 2);
        ConfigStore configs = new DefaultConfigStore();
        configs.and("STATUS", 3);

        //确定更新条件的 按指定条件更新
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2 WHERE 1=1 AND STATUS = 3
        service.update("CRM_USER", row, configs);

        //没有指定条件的 按主键更新
        //UPDATE CRM_USER SET STATUS = 1, CODE = 2 WHERE 1=1 AND ID = 10
        service.update("CRM_USER", row);
    }

    @Test
    public void updateEntity(){
        User user = new User();
        user.setId(1L);
        user.setName("zh");
        ConfigStore configs = new DefaultConfigStore();
        configs.and("CODE", 3);
        //确定更新条件的 按指定条件更新
        //UPDATE CRM_USER SET name = 'zh' WHERE 1=1 AND CODE = 3
        service.update(user, configs);

        //没有指定条件的 按主键更新
        //UPDATE CRM_USER SET name = 'zh' WHERE 1=1 AND id = 1
        service.update(user);
    }

    @Test
    public void sql(){
        String sql = "${insert into crm_user(code)values('???')}";
        service.execute(sql);
        sql = "${insert into crm_user(code, status)values('???', ?)}";
        service.execute(sql, new DefaultConfigStore().params(1));
    }

    public static void main(String[] args) throws Exception {
        String reg = "[^']+(\\?=('|$))";
       // reg = "(?:\"[^\"]+\"|[^\" ]+)\\?(\\s+)";
        reg = "(?:'[^']*'|\"[^\"]*\")";
        reg = "'.*?\\?.*?'";
        String sql = "${insert into crm_user(code)values('1+1=???','?',\"?\",?)}";
        List<String> keys = RegularUtil.fetch(sql, reg, Regular.MATCH_MODE.CONTAIN, 0);
        System.out.println(keys);
        String tmp = BeanUtil.concat(keys);
        keys = RegularUtil.fetch(tmp, "\\?", Regular.MATCH_MODE.CONTAIN, 0);
        System.out.println("引号内:"+keys.size());

        keys = RegularUtil.fetch(sql, "\\?", Regular.MATCH_MODE.CONTAIN, 0);
        System.out.println("共:"+keys.size());

    }
    @Test
    public void group_sql() throws Exception{
        String sql = "SELECT MAX(ID) AS MAX_ID , CODE FROM CRM_USER";
        for(int i=0; i<100; i++) {
            ServiceProxy.querys(sql, new DefaultConfigStore().group("CODE").order("CODE").having("COUNT(*)>1"));
        }
    }

    @Test
    public void batch() throws Exception{
        Table table = service.metadata().table("crm_user");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table<>("CRM_USER");
        table.addColumn("ID", "bigint").autoIncrement(true).primary(true);
        table.addColumn("CODE","varchar(20)");
        table.addColumn("NAME","varchar(20)");
        service.ddl().create(table);

        String sql = "insert into crm_user(code,name)values(#{code},#{name})";
        service.execute(sql, "code:1","name:2");
        List<Object> lines = new ArrayList<>();
        for(int i=0; i<10; i++){
            List<Object> line = new ArrayList<>();
            line.add("1"+i);
            line.add("2"+i);
            lines.add(line);
        }
        sql = "insert into crm_user(code,name)values(?,?)";
        service.execute(3, sql, lines);

        List<Object> arrays = new ArrayList<>();
        for(int i=20; i<30; i++){
            Object[] line = new Object[2];
            line[0] = "code"+i;
            line[1] = "name"+i;
            arrays.add(line);
        }
        service.execute(3, sql, arrays);

        sql = "insert into crm_user(code,name)values(#{code},#{name})";
        Collection<Object> list = new ArrayList<>();
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            //或User user = new User("code","name");
            row.put("CODE", "code"+i);
            row.put("NAME", "name"+i);
            list.add(row);
        }
        service.execute(3, sql, list);

    }
    @Test
    public void or(){

        String val = "admin";
        String id = "123";
        ConfigStore configs = new DefaultConfigStore();
        configs.ne("ID", id);
        ConfigStore act = new DefaultConfigStore();
        act.and("LOGIN_ACCOUNT", val).or("LOGIN_MOBILE", val).or("LOGIN_IDCARD", val).or("LOGIN_MAIL", val);
        configs.and(act);

        service.query("PW_USER", configs );
    }

    @Test
    public void in(){
        service.querys("CRM_USER", new DefaultConfigStore().and("ID","2")
            .and("ID", "1", true, true));		ConfigStore configs = new DefaultConfigStore();

    }
    @Test
    public void cols(){
        service.querys("CRM_USER", new DefaultConfigStore().and("ID","2").columns("ID","SUM(ID) AS QTY").group("ID")
                .and("ID", "1", true, true));
    }
    @Test
    public void findInSet(){
        ConfigStore configs = new DefaultConfigStore();
        Config config = new DefaultConfig();
        config.setCompare(Compare.FIND_IN_SET_OR);
        config.setKey("CODE");
        config.setVariable("CODE");
        config.setValue( "1,2,3".split(","));
        configs.and(config);
        //configs.and(Compare.FIND_IN_SET_AND, "code", "1,2,3".split(","));
        ServiceProxy.querys("bs_art", configs);
    }
    @Test
    public void generator(){
        ConfigTable.IS_UPPER_KEY = false;
        ConfigTable.DEFAULT_PRIMARY_KEY = "id";
        DataRow row = new DataRow();
        row.put("name", "z");
        GeneratorConfig.put(PrimaryGenerator.GENERATOR.SNOWFLAKE);
        service.insert("CRM_USER", row);
        System.out.println(row);
    }
    @Test
    public void insert_map(){
        List<Map<String,Object>> list = new ArrayList<>();
        for(int i=0; i<100;i ++){
            Map<String,Object> map = new HashMap<>();
            map.put("name", "A"+i);
            list.add(map);
            //service.insert( "CRM_USER", map);
        }
        service.insert(10, "CRM_USER", list);

    }
    @Test
    public void condition(){


        ConfigStore configStore = new DefaultConfigStore();
        configStore.eq("age", 1);
        configStore.eq("name", 2);

        DefaultConfigStore where2 = new DefaultConfigStore();
        where2.eq("t2.name", 123);
        configStore.or(where2);
        System.out.println(configStore.getRunText(service.getDao().runtime(), false));
    }
}
